package cn.gx.kevin.dao.impl;

import cn.gx.kevin.common.utils.*;
import com.alibaba.druid.pool.DruidDataSource;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import cn.gx.kevin.common.thread.DaoDBKeyHolder;
import cn.gx.kevin.dao.IDynamicDao;
import cn.gx.kevin.domain.*;
import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/***
 *@description : 表名,字段，条件参数均为动态传值的dao封装
 *@author : kevin.huang
 *@date : 2019/10/26 19:24
 @modified by dynmicDao
 ***/
@Repository(value="dynamicDao")
public class DynamicDao extends DynamicDaoSupport implements IDynamicDao {


    static Pattern sqlVarReg = Pattern.compile("(!?>?<?=?(not)?\\s*(in)?\\s*)\\$\\w+(\\s*[-+]\\s*\\d+[ymd])*");

    public final Logger logger = LoggerFactory.getLogger(DynamicDao.class);

    private final  static String SQL_ID_insertParam = "cn.gx.kevin.dynamicdao.insertParam";

    private final  static String SQL_ID_updateParam = "cn.gx.kevin.dynamicdao.updateParam";

    private final  static String SQL_ID_selectParam = "cn.gx.kevin.dynamicdao.selectParam";

    private final  static String SQL_ID_countParam = "cn.gx.kevin.dynamicdao.countParam";

    private final  static String SQL_ID_insertBatch = "cn.gx.kevin.dynamicdao.insertBatch";

    private final  static String SQL_ID_deleteParam = "cn.gx.kevin.dynamicdao.deleteParam";

    private final  static String SQL_ID_computedParam = "cn.gx.kevin.dynamicdao.computedParam";

    private final  static String SQL_ID_selectOnSql = "cn.gx.kevin.dynamicdao.selectDiy";

    private final  static String SQL_ID_createTempTable = "cn.gx.kevin.dynamicdao.createTempTable";

    private final  static String SQL_ID_dropDatabaseTable = "cn.gx.kevin.dynamicdao.dropDatabaseTable";

    private final  static String SQL_ID_truncateTable = "cn.gx.kevin.dynamicdao.truncateTable";



    /**
     * 切换到指定数据库
     ***/
    public void setDbKey(String key) {
        DaoDBKeyHolder.setKey(key);
    }

    /**
     * 恢复到默认数据库
     ***/
    public void clearDbKey() {
        DaoDBKeyHolder.clear();
    }

    private DynamicDiySelectParam getDySqlParams(String sql, Map<String, Object> params){
        DynamicSqlAnalyseRet res =  buildSqlAndParams(sql,params,"params." ,"#");
        Map<String ,Object> exeParams = res.getParams();
        sql =  res.getSql();
        DynamicDiySelectParam diySelectParam = new DynamicDiySelectParam();
        diySelectParam.setSqlBody(sql);
        diySelectParam.setParams(exeParams);
        return diySelectParam;
    }

    public   DynamicSqlAnalyseRet buildSqlAndParams(String sql ,Map<String, Object> params,String paramPrex,String paramStyle){
        if(params == null){
            params = new HashMap<>();
        }
        if(paramPrex == null){
            paramPrex = "";
        }
        boolean is$ = paramStyle.equals("$");
        if(is$){
            paramStyle = "\\"+paramStyle;
        }
        DynamicSqlAnalyseRet res = new DynamicSqlAnalyseRet();
        Matcher  matcher =   sqlVarReg.matcher(sql);
        int count =1;
        StringBuffer  builder = new StringBuffer();
        Map<String,Object> paramsMap = new HashMap<>();
        while(matcher.find()){
            ParamRet ret =  getParamValue(matcher,params);
            Object paramValue =  ret.getParamValue();
            // String sqlParamName = ret.getParamName();
            String sqlOp = ret.getSqlOp();
            String paramName;
            if(paramValue != null){/****  没有传参值，去掉该参数*********/
                boolean isList =  paramValue instanceof  List;
                if(isList || paramValue.getClass().isArray()){
                    StringBuffer buffer = new StringBuffer();
                    if(sqlOp.contains("!=")){
                        buffer.append(" not in (");
                    }else if(sqlOp.contains("=")){
                        buffer.append(" in (");
                    }
                    if(isList){
                        List<Object> listParam = (List<Object>)paramValue;
                        for(int i =0,len = listParam.size(); i < len ;++i){
                            buffer.append(paramStyle+"{"+paramPrex+"p"+count+"}");
                            paramName = "p"+count;
                            paramsMap.put(paramName,listParam.get(i));
                            if(i < len -1){
                                buffer.append(",");
                            }
                            count++;
                        }
                    }else{
                        Object []  array =( Object[])paramValue;
                        for(int i =0,len = array.length; i < len ;++i){
                            buffer.append(paramStyle+"{"+paramPrex+"p"+count+"}");
                            paramName = "p"+count;
                            paramsMap.put(paramName,array[i]);
                            if(i < len -1){
                                buffer.append(",");
                            }
                            count++;
                        }
                    }
                    buffer.append(")");
                    matcher.appendReplacement(builder, buffer.toString());
                }else{
                    if(is$){
                        if(is$ && paramValue instanceof  Date){
                            Date vale = (Date)paramValue;
                            paramValue = DateUtils.formatDateTime(vale,"yyyy-MM-dd HH:mm:ss");
                        }
                        matcher.appendReplacement(builder,sqlOp + "'"+paramValue+"'");
                    }else{
                        matcher.appendReplacement(builder,sqlOp + paramStyle+"{"+paramPrex+"p"+count+"}");
                        paramName = "p"+count;
                        paramsMap.put(paramName,paramValue);
                    }
                }
            }
            count++;
        }
        matcher.appendTail(builder);
        String buildSql = builder.toString();
        buildSql = buildSql.replaceAll("(and|or)*\\s*\\w+\\s*=\\s*\\$\\w+(\\s*[-+]\\s*\\d+[ymd])*"," ");
        buildSql = buildSql.replaceAll("where\\s*(and|or)","where ");
        res.setSql(buildSql);
        res.setParams(paramsMap);
        return  res;
    }

    private ParamRet getParamValue(Matcher  matcher,Map<String, Object> params){
        matcher.start() ;
        matcher.end();
        String findStr = matcher.group(0);
        String findOp = matcher.group(1);
        findStr = findStr.replace(findOp,"");
        String feildKey = findStr.replace("$","");
        Object paramValue = null;
        /***** 优先取外部参数*******/
        if(params.containsKey(feildKey)){
            paramValue = params.get(feildKey);
        }else{/****  时间计算参数 *****/
            paramValue = DynamicSqlHelper.getValueByComputed(feildKey.toUpperCase());
        }
        ParamRet ret = new ParamRet();
        ret.setSqlOp(" " +findOp + " ");
        ret.setParamName(findStr);
        ret.setParamValue(paramValue);
        return ret;
    }

    class ParamRet{

        String sqlOp;

        public String getSqlOp() {
            return sqlOp;
        }

        public void setSqlOp(String sqlOp) {
            this.sqlOp = sqlOp;
        }

        Object paramValue;

        public Object getParamValue() {
            return paramValue;
        }

        public void setParamValue(Object paramValue) {
            this.paramValue = paramValue;
        }

        public String getParamName() {
            return paramName;
        }

        public void setParamName(String paramName) {
            this.paramName = paramName;
        }
        String paramName;

    }
    private List<LinkedHashMap<String,Object>> toUplowcase( List<LinkedHashMap<String,Object>> tmp){
        List<LinkedHashMap<String,Object>> res = new ArrayList<>();
        for(LinkedHashMap<String,Object> row : tmp){
            LinkedHashMap<String,Object> newRow = new LinkedHashMap<>();
            res.add(newRow);
            for(Map.Entry<String,Object> entry : row.entrySet()){
                newRow.put(entry.getKey().toUpperCase(),entry.getValue());
            }
        }
        return res;
    }

    @Override
    public int insertBatch(DynamicBatchInsertParam params) throws Exception {
        params.setDbType(this.getDbType());
        SqlSession sqlSession = this.getSqlSession();
        if(logger.isDebugEnabled()){
            DruidDataSource ds = (DruidDataSource) sqlSession.getConfiguration().getEnvironment().getDataSource();
            String dbUrl = ds.getUrl();
            logger.debug(">>>>>>>>>>>>>>>>>>>>>>> exec insertBatch from the url {} ,username = {} ",dbUrl,ds.getUsername());
        }
        return sqlSession.insert(SQL_ID_insertBatch,params);
    }

    @Override
    public int insert(DynamicInsertParam params) throws Exception {
        return this.getSqlSession().insert(SQL_ID_insertParam,params);
    }

    @Override
    public int update(DynamicUpdateParam params) throws Exception {
        return this.getSqlSession().insert(SQL_ID_updateParam,params);
    }


    @Override
    public int delete(DynamicDeleteParam params) throws Exception {
        return this.getSqlSession().delete(SQL_ID_deleteParam,params);
    }


    @Override
    public int count(DynamicCountParam params) throws Exception {
        SqlSession sqlSession = params.getSqlSession();
        if(sqlSession == null){
            sqlSession = this.getSqlSession();
        }
        return sqlSession.selectOne(SQL_ID_countParam,params);
    }

    @Override
    public List<LinkedHashMap<String,Object>> select(DynamicSelectParam params) throws Exception {
        SqlSession sqlSession = params.getSqlSession();
        if(sqlSession == null){
            sqlSession = this.getSqlSession();
        }
        List<LinkedHashMap<String,Object>> tmp = this.dySelect(params,SQL_ID_selectParam,sqlSession);
        return tmp;
    }

    @Override
    public List<LinkedHashMap<String,Object>> computedParam(DynamicComputedParam params) throws Exception {
        SqlSession sqlSession = params.getSqlSession();
        if(sqlSession == null){
            sqlSession = this.getSqlSession();
        }
        List<LinkedHashMap<String,Object>> tmp = this.dySelect(params,SQL_ID_computedParam,sqlSession);
        return tmp;
    }

    protected List<LinkedHashMap<String,Object>> dySelect(Object params,String sqlId)throws Exception {
        return this.dySelect(params,sqlId,this.getSqlSession());
    }


    protected List<LinkedHashMap<String,Object>> dySelect(Object params,String sqlId,SqlSession sqlSession)throws Exception {
        List<LinkedHashMap<String,Object>> res = new ArrayList<LinkedHashMap<String,Object>>();
        try{
            sqlSession.select(sqlId,params,new ResultHandler(){
                @Override
                public void handleResult(ResultContext resultContext) {
                    LinkedHashMap<String,Object> rowData = (LinkedHashMap<String,Object>)resultContext.getResultObject();
                    LinkedHashMap<String,Object> row = new LinkedHashMap<String,Object> ();
                    for(Map.Entry<String,Object> entry : rowData.entrySet()){
                        row.put(entry.getKey().toUpperCase(),entry.getValue());
                    }
                    res.add(row);
                }
            });
        }catch (Exception ex){
            logger.error("",ex);
        }
        return res;
    }

    @Override
    public DynamicPagingResult selectOnSql(String sql,int page, int pageSize, Map<String, Object> params) throws Exception {
        DynamicDiySelectParam diySelectParam =   getDySqlParams(sql,params);
        Object sqlSession1 = params.get("sqlSession");
        SqlSession sqlSession;
        if(sqlSession1 == null){
            sqlSession = this.getSqlSession();
        }else{
            sqlSession =(SqlSession) sqlSession1;
        }
        DynamicPagingResult result = _pagin(SQL_ID_selectOnSql,page,pageSize,diySelectParam,sqlSession);
        return result;
    }



    @Override
    public List<LinkedHashMap<String, Object>> selectOnSqlNotPagin(String sql, Map<String, Object> params) throws Exception {
        DynamicDiySelectParam diySelectParam =   getDySqlParams(sql,params);
        Object sqlSession1 = params.get("sqlSession");
        SqlSession sqlSession;
        if(sqlSession1 == null){
            sqlSession = this.getSqlSession();
        }else{
            sqlSession =(SqlSession) sqlSession1;
        }
        List<LinkedHashMap<String,Object>> resList =  sqlSession.selectList(SQL_ID_selectOnSql, diySelectParam);
        return resList;
    }

    @Override
    public DynamicPagingResult selectPagin(int page, int pageSize, DynamicSelectParam params) throws Exception {
        SqlSession sqlSession = params.getSqlSession();
        if(sqlSession == null){
            sqlSession = this.getSqlSession();
        }
        DynamicPagingResult result = _pagin(SQL_ID_selectParam,page,pageSize,params,sqlSession);
        return result;
    }

    @Override
    public DynamicPagingResult selectPaginComputed(int page, int pageSize, DynamicComputedParam params) throws Exception {
        SqlSession sqlSession = params.getSqlSession();
        if(sqlSession == null){
            sqlSession = this.getSqlSession();
        }
        DynamicPagingResult result = _pagin(SQL_ID_computedParam,page,pageSize,params,sqlSession);
        return result;
    }

    private DynamicPagingResult _pagin(String sqlId,int page, int pageSize,Object params,SqlSession s){
        RowBounds rowBounds = new RowBounds((page - 1) * pageSize, pageSize);
        PageHelper.startPage(page, pageSize);
        if(logger.isDebugEnabled()){
            DruidDataSource ds = (DruidDataSource) s.getConfiguration().getEnvironment().getDataSource();
            String dbUrl = ds.getUrl();
            logger.debug(">>>>>>>>>>>>>>>>>>>>>>> exec _pagin from the url {} ,userName = {} ",dbUrl,ds.getUsername());
        }
        List<LinkedHashMap<String,Object>> resList =  s.selectList(sqlId, params, rowBounds);
        PageInfo<LinkedHashMap<String,Object>> pageResult = new PageInfo<LinkedHashMap<String,Object>>(resList);
        long totalSize = pageResult.getTotal();
        DynamicPagingResult result = new DynamicPagingResult();
        List<LinkedHashMap<String,Object>> tmp = pageResult.getList();
        tmp = toUplowcase(tmp);
        result.setList(tmp);
        result.setTotal(totalSize);
        result.setPage(page);
        result.setPageSize(pageSize);
        return result;
    }

    @Override
    public SqlSession dropDatabaseTable(String tableName,SqlSession session) throws Exception {
        Map<String, String> paramsMap = new HashMap<>();
        String dbType = this.getDbType();
        paramsMap.put("tableName",tableName);
        paramsMap.put("dbType",dbType);
        SqlSession s;
        if(session != null){
            s = session;
        }else {
            s = this.getSqlSession();
        }
        try{
            if("oracle".equalsIgnoreCase(dbType)){ //先执行truncateTable
                paramsMap.put(SysConstants.forceExeSqlParamKey,"1");
                s.update(SQL_ID_truncateTable,paramsMap);
            }

            s.update(SQL_ID_dropDatabaseTable,paramsMap);
        }catch (Exception x){
            logger.warn("",x);
        }
        return s;
    }

    @Override
    public SqlSession createTempTable(String createSql,SqlSession session) throws Exception {
        Map<String,Object> paramsMap = new HashMap<>();
        return this.createTempTable(createSql,session,paramsMap);
    }

    @Override
    public SqlSession createTempTable (String createSql,SqlSession session,Map<String,Object> params) throws Exception{
        params.put("createSql",createSql);
        params.put("dbType",this.getDbType());
        SqlSession s;
        if(session != null){
            s = session;
        }else {
            s = this.getSqlSession();
        }
        params.put(SysConstants.forceExeSqlParamKey,"1");
        s.update(SQL_ID_createTempTable,params);
        return  s;
    }

    @Override
    public SqlSession executeInsertIntoSql(String sql, Map<String, Object> params, SqlSession session) throws Exception {
        return this.createTempTable(sql,session,params);
    }
}
